IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[GetDisclosuresByCOIOrgAdmPersonID]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.GetDisclosuresByCOIOrgAdmPersonID
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/***************************************************************                            
* Name:   [GetDisclosuresByCOIOrgAdmPersonID]                            
* Purpose:  Retrieves the Dislcosures for COI Users matching the filter parameters.
* @admPersonIds     admPersonIds of the users                                      
* @COIOrgIds		COI Organization Ids
* @OrderBy			OrderBy column
* @PageSize			Page size, generally the number of columns to retrieve 
* @PageStartIndex	Starting page number
* @PageEndIndex		Ending page number
*********************************************************************/    
CREATE PROCEDURE [dbo].[GetDisclosuresByCOIOrgAdmPersonID]                                
(                                
@admPersonIds VARCHAR(2000) = null,                                
@COIOrgIds  VARCHAR(2000)= NULL,
@OrderBy VARCHAR(20) = NULL,
@PageSize INT = 10,
@PageStartIndex INT = 0,
@PageEndIndex INT = 1
)                                
AS BEGIN                                
	SET NOCOUNT ON 
	
	IF(@OrderBy IS NULL)
	BEGIN
		SET @OrderBy = 'LastName'
	END	

	--If null then we consider all the records
	IF(@admPersonIds IS NULL OR @admPersonIds = '')
	BEGIN
		SET @admPersonIds = 'coi.AdmPersonId'
	END
	--If null then we consider all the records
	IF(@COIOrgIds IS NULL OR @COIOrgIds = '')
	BEGIN
		SET @COIOrgIds = 'qa.[Name]'
	END

	DECLARE @strSql_Count NVARCHAR(2000)
	DECLARE @strSql NVARCHAR(2000)
	SET @strSql = ''
	SET @strSql_Count = ''
	DECLARE @TotalRecords INT
	DECLARE @ParmDefinition NVARCHAR(100)
	
	
	SET @strSql_Count = @strSql_Count + 'SELECT '
	SET @strSql_Count = @strSql_Count + 'DISTINCT coi.id AS ''COIId'',adp.Id AS ''admPersonId'',adp.LastName AS ''LastName'',adp.FirstName AS ''FirstName'','
	SET @strSql_Count = @strSql_Count + 'qa.ExternalId AS ''OrgId'',qa.[Name] AS ''OrgName'', dbo.GetCOIActivitiesByCOIIdAndOrgName(coi.id,qa.Name) AS ''TypeOfInterest'''
	SET @strSql_Count = @strSql_Count + ' FROM QuestionnaireAnswer qa '
	SET @strSql_Count = @strSql_Count + 'INNER JOIN coi_questionnaireanswer_map coiqmap  on coiqmap.questionnaireAnswerId = qa.id '
	SET @strSql_Count = @strSql_Count + 'INNER JOIN ConflictOfInterest coi on coi.id = coiqmap.coiid '
	SET @strSql_Count = @strSql_Count + 'INNER JOIN admPerson adp on adp.id = coi.admPersonId '
	SET @strSql_Count = @strSql_Count + 'INNER JOIN QuestionnaireVersion qv on qv.id = qa.QuestionnaireVersionId '
	SET @strSql_Count = @strSql_Count + 'INNER JOIN Questionnaire q on q.id = qv.QuestionnaireId '
	SET @strSql_Count = @strSql_Count + 'INNER JOIN COIOrganization coiOrg on coiOrg.Id = qa.ExternalId '
	SET @strSql_Count = @strSql_Count + 'WHERE '
	SET @strSql_Count = @strSql_Count + 'ISNULL(CAST(coi.[Id] as varchar(10)),''*'') =(SELECT ISNULL(CAST(MAX(Id)AS VARCHAR(10)),''*'') AS LatestCOIId FROM conflictofinterest WHERE coi.admPersonId = AdmPersonId AND Status <> ''COISTATUS_TERMINATED'') '            
	SET @strSql_Count = @strSql_Count + ' AND '
	SET @strSql_Count = @strSql_Count + ' (coiOrg.Reviewed = 0 OR (coiOrg.Reviewed = 1 AND coiOrg.Active = 1))'
	SET @strSql_Count = @strSql_Count + ' AND '
	SET @strSql_Count = @strSql_Count + ' coi.AdmPersonId IN (' + @admPersonIds + ')'
	SET @strSql_Count = @strSql_Count + ' AND '
	SET @strSql_Count = @strSql_Count + ' qa.[Name] IN (' + @COIOrgIds + ')'

	SET @strSql = 'SELECT @TotalRecords = COUNT(*) FROM ( ' + @strSql_Count + ') AS DerivedTable'
	
	SET @ParmDefinition = '@TotalRecords INT OUT'

--	PRINT @strSql	

	EXEC sys.sp_executesql @strSql, @ParmDefinition, @TotalRecords OUT 

--	Print @TotalRecords

	SET @strSql = ''
	SET @strSql = @strSql + 'SELECT '
	SET @strSql = @strSql + 'COIId,admPersonId,LastName,FirstName,OrgId,OrgName,TypeOfInterest, '
	SET @strSql = @strSql + 'RecordCount '
	SET @strSql = @strSql + 'FROM ('	
	SET @strSql = @strSql + 'SELECT '
	SET @strSql = @strSql + 'COIId,admPersonId,LastName,FirstName,OrgId,OrgName,TypeOfInterest, '
	SET @strSql = @strSql + CAST(@TotalRecords AS NVARCHAR(10)) + ' AS ''RecordCount'', '
	SET @strSql = @strSql + 'ROW_NUMBER() OVER (ORDER BY ' +   @OrderBy + ') AS ROWNUM'
	SET @strSql = @strSql + ' FROM ('
	SET @strSql = @strSql + @strSql_Count
    SET @strSql = @strSql + ') AS InnerQueryResult'
	SET @strSql = @strSql + ') AS FinalQueryResult'
	SET @strSql = @strSql + ' WHERE '
	SET @strSql = @strSql + 'ROWNUM BETWEEN ' +  CAST((@PageSize*@PageStartIndex) + 1 AS VARCHAR(10)) + ' AND ' + CAST(@PageSize*@PageEndIndex AS VARCHAR(10))

	EXEC(@strSql)

--	PRINT @strSql
END


--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
-- Executing the Stored Procedure using the Exec statment below produces the SQL below Exec statetme.
------------------------------------  Explaination of the SQL produced Dynamically  --------------------------------
--1. Contains three SQL SELECT Statements.
--2. The Innermost SQL Select statement gets the Distinct rows based on the paremeters for the WHERE Clause.
--   Its named as InnerQueryResult
--3. The SELECT statement that wraps the InnerQueryResult uses the SQL Ranking functions to do the Custom paging and 
--   sorting. The RowNum column is selected in this select statement. This SELECT is named as FinalQueryResult
--4. The Outer most select query selects the Results from the 'FinalQueryResult' inline table and implements the 
--   paging using the ROWNUM BETWEEN X AND Y
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------

--Exec GetDisclosuresByCOIOrgAdmPersonID @PageSize = 30, @COIOrgIds = '''Pfizer, Inc.''',@OrderBy = 'LastName'

--SELECT COIId,admPersonId,LastName,FirstName,OrgId,OrgName,TypeOfInterest,RecordCount--,ROWNUM 
--FROM 
--(
--	SELECT COIId,admPersonId,LastName,FirstName,OrgId,OrgName,TypeOfInterest, 35 AS 'RecordCount', 
--	ROW_NUMBER() OVER (ORDER BY LastName) AS ROWNUM FROM 
--	(
--		SELECT DISTINCT coi.id AS 'COIId',
--		adp.Id AS 'admPersonId',adp.LastName AS 'LastName',adp.FirstName AS 'FirstName',
--		qa.ExternalId AS 'OrgId',qa.[Name] AS 'OrgName', dbo.GetCOIActivitiesByCOIIdAndOrgName(coi.id,qa.Name) AS 'TypeOfInterest'
--		FROM 
--		QuestionnaireAnswer qa 
--		INNER JOIN coi_questionnaireanswer_map coiqmap  on coiqmap.questionnaireAnswerId = qa.id 
--		INNER JOIN ConflictOfInterest coi on coi.id = coiqmap.coiid 
--		INNER JOIN admPerson adp on adp.id = coi.admPersonId 
--		INNER JOIN QuestionnaireVersion qv on qv.id = qa.QuestionnaireVersionId 
--		INNER JOIN Questionnaire q on q.id = qv.QuestionnaireId 
--		INNER JOIN COIOrganization coiOrg on coiOrg.Id = qa.ExternalId 
--		WHERE 
--		ISNULL(CAST(coi.[Id] as varchar(10)),'*') =(SELECT ISNULL(CAST(MAX(Id)AS VARCHAR(10)),'*') AS LatestCOIId FROM conflictofinterest WHERE coi.admPersonId = AdmPersonId AND Status <> 'COISTATUS_TERMINATED')
--		AND (coiOrg.Reviewed = 0 OR (coiOrg.Reviewed = 1 AND coiOrg.Active = 1)) 
--		AND  coi.AdmPersonId IN (coi.AdmPersonId) 
--		AND  qa.[Name] IN ('Pfizer, Inc.')
--	) AS InnerQueryResult
--) AS FinalQueryResult
-- WHERE ROWNUM BETWEEN 1 AND 30
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------

